package com.tdam2014.grupo3.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	// DB Name
	public static final String dbName = "flickr_viewer_db";
	
	// Table Peoples
	public static final String tblPeople = "peoples";
	public static final String colPeopleNSID = "nsid";
	public static final String colPeopleUsername = "username";
	public static final String colPeopleRealname = "real_name";
	public static final String colPeopleIconserver = "icon_server";
	public static final String colPeopleIconfarm = "icon_farm";
	public static final String colPeopleCountPhotoSets = "count_photosets";
	
	// Table PhotoSets
	public static final String tblPhotoSet = "photosets";
	public static final String colPhotoSetPeopleNSID = "people_nsid";
	public static final String colPhotoSetID = "id";
	public static final String colPhotoSetTitle = "title";
	public static final String colPhotoSetCreated = "date_created";
	public static final String colPhotoSetCountPhotos = "count_photos";
	
	// Table Photos
	public static final String tblPhoto = "photos";
	public static final String colPhotoPhotoSetID = "photoset_id";
	public static final String colPhotoID = "id";
	public static final String colPhotoTitle = "title";
	public static final String colPhotoPosted = "date_posted";
	public static final String colPhotoSecret = "secret";
	public static final String colPhotoFarm = "farm";
	public static final String colPhotoServer = "server";
	public static final String colPhotoCountComments = "count_comments";
	
	// Table Comments
	public static final String tblComment = "comments";
	public static final String colCommentPhotoID = "photo_id";
	public static final String colCommentID = "id";
	public static final String colCommentAuthorname = "author_name";
	public static final String colCommentDateCreated = "date_created";
	public static final String colCommentText = "text";
	
	public DatabaseHelper(Context context) {
		super(context, dbName, null, 4);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		
		// Create Table Peoples
		db.execSQL("CREATE TABLE " + tblPeople + " ("
				+ colPeopleNSID + " TEXT PRIMARY KEY,"
				+ colPeopleUsername + " TEXT,"
				+ colPeopleRealname + " TEXT,"
				+ colPeopleIconserver + " INTEGER,"
				+ colPeopleIconfarm + " INTEGER,"
				+ colPeopleCountPhotoSets + " INTEGER)");
		
		// Create Table PhotoSets
		db.execSQL("CREATE TABLE " + tblPhotoSet + " ("
				+ colPhotoSetPeopleNSID + " TEXT,"
				+ colPhotoSetID + " TEXT PRIMARY KEY,"
				+ colPhotoSetTitle + " TEXT,"
				+ colPhotoSetCreated + " INTEGER,"
				+ colPhotoSetCountPhotos + " INTEGER)");
				
		// Create Table Photos
		db.execSQL("CREATE TABLE " + tblPhoto + " ("
				+ colPhotoPhotoSetID + " TEXT,"
				+ colPhotoID + " TEXT PRIMARY KEY,"
				+ colPhotoTitle + " TEXT,"
				+ colPhotoPosted + " INTEGER,"
				+ colPhotoFarm + " INTEGER,"
				+ colPhotoSecret + " TEXT,"
				+ colPhotoServer + " TEXT,"
				+ colPhotoCountComments + " INTEGER)");
				
		// Create Table Comments
		db.execSQL("CREATE TABLE " + tblComment + " ("
				+ colCommentPhotoID + " TEXT,"
				+ colCommentID + " TEXT PRIMARY KEY,"
				+ colCommentAuthorname + " TEXT,"
				+ colCommentDateCreated + " INTEGER,"
				+ colCommentText + " TEXT)");
		
		// Trigger PhotoSets inserts (FK Persons)
		db.execSQL("CREATE TRIGGER insert_photoset_fk_to_people"
				+ " BEFORE INSERT"
				+ " ON " + tblPhotoSet
				+ " FOR EACH ROW"
				+ " BEGIN"
				+ " SELECT CASE WHEN ((SELECT " + colPeopleNSID + " FROM " + tblPeople + " WHERE " + colPeopleNSID + "=new." + colPhotoSetPeopleNSID + " ) IS NULL)"
				+ " THEN RAISE (ABORT,'Foreign Key Violation')"
				+ " END;"
				+ " END;");
		
		// Trigger Photos inserts (FK PhotoSets)
		db.execSQL("CREATE TRIGGER insert_photo_fk_to_photoset"
				+ " BEFORE INSERT"
				+ " ON " + tblPhoto
				+ " FOR EACH ROW"
				+ " BEGIN"
				+ " SELECT CASE WHEN ((SELECT " + colPhotoSetID + " FROM " + tblPhotoSet + " WHERE " + colPhotoSetID + "=new." + colPhotoPhotoSetID + " ) IS NULL)"
				+ " THEN RAISE (ABORT,'Foreign Key Violation')"
				+ " END;"
				+ " END;");
		
		// Trigger Comments inserts (FK Photos)
		db.execSQL("CREATE TRIGGER insert_comment_fk_to_photo"
				+ " BEFORE INSERT"
				+ " ON " + tblComment
				+ " FOR EACH ROW"
				+ " BEGIN"
				+ " SELECT CASE WHEN ((SELECT " + colPhotoID + " FROM " + tblPhoto + " WHERE " + colPhotoID + "=new." + colCommentPhotoID + " ) IS NULL)"
				+ " THEN RAISE (ABORT,'Foreign Key Violation')"
				+ " END;"
				+ " END;");
		
		// Trigger Persons deletes (Delete PhotoSets too)
		db.execSQL("CREATE TRIGGER delete_person"
				+ " BEFORE DELETE"
				+ " ON " + tblPeople
				+ " FOR EACH ROW"
				+ " BEGIN"
				+ " DELETE FROM " + tblPhotoSet + " WHERE " + colPhotoSetPeopleNSID + "=old." + colPeopleNSID + ";"
				+ " END");
		
		// Trigger PhotoSets deletes (Delete Photos too)
		db.execSQL("CREATE TRIGGER delete_photoset"
				+ " BEFORE DELETE"
				+ " ON " + tblPhotoSet
				+ " FOR EACH ROW"
				+ " BEGIN"
				+ " DELETE FROM " + tblPhoto + " WHERE " + colPhotoPhotoSetID + "=old." + colPhotoSetID + ";"
				+ " END");
		
		// Trigger Photos deletes (Delete Comments too)
		db.execSQL("CREATE TRIGGER delete_photo"
				+ " BEFORE DELETE"
				+ " ON " + tblPhoto
				+ " FOR EACH ROW"
				+ " BEGIN"
				+ " DELETE FROM " + tblComment + " WHERE " + colCommentPhotoID + "=old." + colPhotoID + ";"
				+ " END");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + tblComment);
		db.execSQL("DROP TABLE IF EXISTS " + tblPhoto);
		db.execSQL("DROP TABLE IF EXISTS " + tblPhotoSet);
		db.execSQL("DROP TABLE IF EXISTS " + tblPeople);
		
		db.execSQL("DROP TRIGGER IF EXISTS insert_photoset_fk_to_people");
		db.execSQL("DROP TRIGGER IF EXISTS insert_photo_fk_to_photoset");
		db.execSQL("DROP TRIGGER IF EXISTS insert_comment_fk_to_photo");
		db.execSQL("DROP TRIGGER IF EXISTS delete_person");
		db.execSQL("DROP TRIGGER IF EXISTS delete_photoset");
		db.execSQL("DROP TRIGGER IF EXISTS delete_photo");
		
		onCreate(db);
	}
	
	public int getLastID() {
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT last_insert_rowid()", null);
		
		if(cursor.moveToFirst()) {
			return cursor.getInt(0);
		}
		
		return -1;
	}
}
